package com.example.demo.controller;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.example.demo.util.RemoveComment;
import com.example.demo.util.SQLTerminalQuerySplit;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.example.demo.entity.BaseResponse;
import com.example.demo.entity.Database;
import com.example.demo.entity.QuestInExer;
import com.example.demo.entity.Teacher;
import com.example.demo.service.DatabaseService;
import com.example.demo.service.QuestInExerService;
import com.example.demo.service.QuestionService;
import com.example.demo.service.StuAnswerService;
import com.example.demo.service.TeacherService;
import com.example.demo.tool.DBConnection;
import com.example.demo.tool.DBDatasource;
import com.example.demo.tool.DBJudgeTool;
import com.example.demo.util.LocalCacheUtil;
import com.example.demo.util.SetDBConnection;

@RequestMapping("/judge")
@CrossOrigin
@RestController
public class JudgeController {
	@Autowired
	private QuestInExerService questInExerService;
	@Autowired
	private StuAnswerService stuAnswerService;
	@Autowired
	private DatabaseService databaseService;
	@Autowired
	private TeacherService teacherService;
	@Autowired
	private DBDatasource dbDatasource;
	
	/* 评判功能入口 */
	@PostMapping(value="/judge")
	public BaseResponse<Map<String, Object>> judgeSQL(@RequestParam(value = "studentId") String studentId,
                                                      @RequestParam(value = "exerciseId") int exerciseId,
                                                      @RequestParam(value = "questionId") int questionId,
                                                      @RequestParam(value = "answer") String answer,
                                                      @RequestParam(value = "idea", required=false) String idea) {
		Map<String, Object> modelMap = new HashMap<>();
		
		if (studentId == null) {
			modelMap.put("errmessage", "学生id不存在");
			modelMap.put("judge result", false);
			return BaseResponse.fail(modelMap);
		}
		
		Map<String, Object> judgeQuestionMap = questInExerService.getJudgeQuestion(exerciseId, questionId);
		if (judgeQuestionMap == null) {
			modelMap.put("errmessage", "题目不存在");
			modelMap.put("judge result", false);
			return BaseResponse.fail(modelMap);
		}
		String correct = (String)judgeQuestionMap.get("answer");
		short questionClass = (short)judgeQuestionMap.get("questionClass");
		String targetName = (String)judgeQuestionMap.get("targetName");
		String initSQL = (String)judgeQuestionMap.get("initSQL");
		int score = (int)judgeQuestionMap.get("score");
		
		String dbName = (String)judgeQuestionMap.get("dbName");
		//获取数据库信息和相应教师信息，由于存在约束，因此不需要验证是否存在
		Database database = databaseService.getDatabaseByName(dbName);
		Teacher teacher = teacherService.getTeacher(database.getUserId());
		
		DBConnection dbConnection = new DBConnection(dbDatasource);
		SetDBConnection.setDBConnection(dbConnection, database, teacher);
		
		Connection connection;
		try {
			 connection = dbConnection.getConnect();
		} catch (SQLException e) {
			e.printStackTrace();
			modelMap.put("errmessage", "数据库连接错误");
			modelMap.put("judge result",false);
			Map<String,Object> updateResultMap=
					stuAnswerService.modifyStuAnswer(studentId,exerciseId,questionId,answer,false,idea,0);
			if("error:not exists".equals((String)updateResultMap.get("state"))){
				updateResultMap = stuAnswerService.addStuAnswer(studentId,exerciseId,questionId,answer,false,idea,0);
			}
			return BaseResponse.fail(modelMap);
		}
		//		String answerHead;
//		String correctHead;
//		if (answer.trim().length() > 6) answerHead = answer.trim().substring(0, 6);
//		else answerHead = answer.trim();
//
//		if (correct.trim().length() > 6) correctHead = correct.trim().substring(0, 6);
//		else correctHead = correct.trim();

//		String check_answer = new String(answer.trim());
//		if (!answerHead.equalsIgnoreCase(correctHead))
//			result = false;
		DBJudgeTool dbJudgeTool = new DBJudgeTool(connection);
		int result=0;

		ArrayList<String> sqlList = new ArrayList<>();
		SQLTerminalQuerySplit sqlTerminalQuerySplit = new SQLTerminalQuerySplit();
		RemoveComment removeComment = new RemoveComment();

		sqlTerminalQuerySplit.splitQuerries(sqlList,answer,false);
		if(sqlList.size()!=1){
			result = 4;
			modelMap.put("errmessage","只支持对一条sql语句进行评判");
		}
		else {
			String check_answer = removeComment.removeComment(sqlList.get(0));

			switch (questionClass) {
			case 1:
				result = dbJudgeTool.checkDQL(check_answer, correct);
				break;
			case 2:
				result = dbJudgeTool.checkDML(check_answer, correct, targetName);
				break;
			case 3:
				result = dbJudgeTool.checkCreateTable(check_answer, targetName, initSQL);
				break;
			case 4:
				result = dbJudgeTool.checkAlterTable(check_answer, targetName, initSQL);
				break;
			case 5:
				result = dbJudgeTool.checkCreateView(check_answer, targetName);
				break;
			case 6:
				result = dbJudgeTool.checkCreateIndex(check_answer, targetName, initSQL);
				break;
			case 7:
				result = dbJudgeTool.checkCreateUser(check_answer, correct, targetName);
				break;
			case 8:
				result = dbJudgeTool.checkGrantTable(check_answer, targetName, initSQL);
				break;
			case 9:
				result = dbJudgeTool.checkRevokeTable(check_answer, targetName, initSQL);
				break;
			case 10:
				result = dbJudgeTool.checkGrantUser(check_answer, targetName, initSQL);
				break;
			case 11:
				result = dbJudgeTool.checkRevokeUser(check_answer, targetName, initSQL);
				break;
			case 12:
				result = dbJudgeTool.checkCreateFunc(check_answer, targetName, questionId);
				break;
			default:
				modelMap.put("errmessage", "无法评判该数据类型");
				modelMap.put("judge result", false);
				return BaseResponse.fail(modelMap);
			}
		}
		switch (result){
			case 0:modelMap.put("judge result",false);modelMap.put("errmessage","结果错误");score=0;break;
			case 1:modelMap.put("judge result",true);break;
			case 2:modelMap.put("judge result",false);modelMap.put("errmessage","语句类型错误");score=0;break;
			case 3:modelMap.put("judge result",false);modelMap.put("errmessage","评判环境初始化失败");score=0;break;
			case 4:modelMap.put("judge result",false);modelMap.put("errmessage","只支持对一条sql语句进行评判");score=0;break;
			case 5:modelMap.put("judge result",false);modelMap.put("errmessage","评判过程出现异常");score=0;break;
			case 10:modelMap.put("judge result",false);modelMap.put("errmessage","create function/procedure类题目只支持select语句");score=0;break;
			default:modelMap.put("judge result",false);modelMap.put("errmessage","未知错误");score=0;
		}
		try {
			dbConnection.close();
		} catch(SQLException e) {
			e.printStackTrace();
		}

		boolean tag=false;
		if(result == 1)
			tag=true;
		Map<String, Object> updateResultMap =
				stuAnswerService.modifyStuAnswer(studentId, exerciseId, questionId, answer, tag, idea, score);
		if ("error:not exists".equals((String)updateResultMap.get("state"))) {
			updateResultMap = stuAnswerService.addStuAnswer(studentId, exerciseId, questionId, answer, tag, idea, score);
		}
		
		String updateStatus = (String)updateResultMap.get("state");
		modelMap.put("insert state", updateStatus);
		
		return BaseResponse.success(modelMap);
	}

	/* 连接评判数据库 ,只用于修改test_case表格 */
	@PostMapping(value = "/connectJudgeDatabase")
	public BaseResponse<Map<String, Object>> connectJudgeDatabase(@RequestParam(value = "teacherId") String teacherId,
																  @RequestParam(value = "zjId",required = false,defaultValue = "#")String zjId,
			                                                      @RequestParam(value = "dbName") String dbName) {
		Map<String, Object> modelMap = new HashMap<>();
		
		//获取数据库连接信息
		Database database = databaseService.getDatabaseByName(dbName);
		if (database == null) {
			modelMap.put("errmessage", dbName + " 数据库不存在");
			return BaseResponse.fail(modelMap);

		}

		
		//获取数据库对应的教师用户信息
		Teacher teacher = teacherService.getTeacher(teacherId);
		
		//建立数据库连接
		DBConnection dbConnection = new DBConnection(dbDatasource);
		SetDBConnection.setDBConnection(dbConnection, database, teacher);
		try {
			dbConnection.getConnect();
		} catch (SQLException e) {
			e.printStackTrace();
//			modelMap.put("errmessage", "database connect error");
			modelMap.put("errmessage", "数据库连接错误");
			return BaseResponse.fail(modelMap);
		}
		
		//如果存在旧连接，关闭旧连接，将新连接存入缓存中
		DBConnection oldDbConnection = (DBConnection) LocalCacheUtil.get(setKeyForJudgeConnect(teacherId));
		if (oldDbConnection != null) {
			try {
				oldDbConnection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			LocalCacheUtil.remove(setKeyForJudgeConnect(teacherId));
		}
		if(zjId.equals("#")) {
			System.out.println("---------------------------------------");
			System.out.println(teacherId);
			LocalCacheUtil.set(setKeyForJudgeConnect(teacherId), dbConnection, 4 * 60 * 60 * 1000);
		}
		else
		{
			System.out.println("---------------------------------------");
			System.out.println(setKeyForJudgeConnect(zjId));
			LocalCacheUtil.set(setKeyForJudgeConnect(zjId), dbConnection, 4 * 60 * 60 * 1000);
			DBConnection dbConnection1 = (DBConnection) LocalCacheUtil.get(setKeyForJudgeConnect(zjId));
			System.out.println(zjId);
			System.out.println(dbConnection1);
		}


		modelMap.put("notice", "数据库连接成功");
		return BaseResponse.success(modelMap);
	}
	
	/* 断开数据库连接 */
	@PostMapping(value = "/disconnectJudgeConnect")
	public BaseResponse<Object> disconnectJudgeConnect(@RequestParam(value = "teacherId") String teacherId) {
		DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(setKeyForJudgeConnect(teacherId));
		if (dbConnection != null) {
			LocalCacheUtil.remove(setKeyForJudgeConnect(teacherId));
			try {
				dbConnection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return BaseResponse.success(null);
	}
	
	/* 新增单条测试用例 */
	@PostMapping(value = "/addOneTestCase")
	public BaseResponse<Map<String, Object>> addOneTestCase(@RequestParam(value = "teacherId") String teacherId,
			                                                @RequestParam(value = "questionId") int questionId,
			                                                @RequestParam(value = "testCase") String test_case) {
		
		Map<String, Object> modelMap = new HashMap<>();
		Map<String, Object> resultMap;
		DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(setKeyForJudgeConnect(teacherId));
		if (dbConnection == null) {
//			modelMap.put("errmessage", "connection timeout, please reconnect database");
			modelMap.put("errmessage", "连接超时, 请重连数据库");
			return BaseResponse.fail(modelMap);
		}
		
		Connection connection;
		try {
			 connection = dbConnection.getConnect();
		} catch (SQLException e) {
//			modelMap.put("errmessage", "can't connect database");
			modelMap.put("errmessage", "无法连接数据库");
			return BaseResponse.fail(modelMap);
		}
		
		DBJudgeTool dbJudgeTool = new DBJudgeTool(connection);
		resultMap = dbJudgeTool.insertFuncTest(questionId, test_case);
		
		if (!"success".equals(resultMap.get("state"))) {
			modelMap.put("judge result", false);
			return BaseResponse.fail(resultMap);
		}
		
		return BaseResponse.success(resultMap);
	}
	
	/* 删除单条测试用例 */
	@PostMapping(value = "/deleteOneTestCase")
	public BaseResponse<Map<String, Object>> deleteOneTestCase(@RequestParam(value = "teacherId") String teacherId,
			                                                @RequestParam(value = "caseId") int caseId,
			                                                @RequestParam(value = "questionId") int questionId) {
		
		Map<String, Object> modelMap = new HashMap<>();
		Map<String, Object> resultMap;
		DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(setKeyForJudgeConnect(teacherId));
		if (dbConnection == null) {
//			modelMap.put("errmessage", "connection timeout, please reconnect database");
			modelMap.put("errmessage", "连接超时, 请重连数据库");
			return BaseResponse.fail(modelMap);
		}
		
		Connection connection;
		try {
			 connection = dbConnection.getConnect();
		} catch (SQLException e) {
//			modelMap.put("errmessage", "can't connect database");
			modelMap.put("errmessage", "无法连接数据库");
			return BaseResponse.fail(modelMap);
		}
		
		DBJudgeTool dbJudgeTool = new DBJudgeTool(connection);
		resultMap = dbJudgeTool.deleteFuncTest(caseId, questionId);
		
		if (!"success".equals(resultMap.get("state"))) {
			return BaseResponse.fail(resultMap);
		}
		
		return BaseResponse.success(resultMap);
	}
	
	/* 删除指定题目全部测试用例 */
	@PostMapping(value = "/deleteAllTestCase")
	public BaseResponse<Map<String, Object>> deleteAllTestCase(@RequestParam(value = "teacherId") String teacherId,
			                                                   @RequestParam(value = "questionId") int questionId) {
		
		Map<String, Object> modelMap = new HashMap<>();
		Map<String, Object> resultMap;
		DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(setKeyForJudgeConnect(teacherId));
		if (dbConnection == null) {
			modelMap.put("errmessage", "连接超时, 请重连数据库");
			return BaseResponse.fail(modelMap);
		}
		
		Connection connection;
		try {
			 connection = dbConnection.getConnect();
		} catch (SQLException e) {
			modelMap.put("errmessage", "无法连接数据库");
			return BaseResponse.fail(modelMap);
		}
		
		DBJudgeTool dbJudgeTool = new DBJudgeTool(connection);
		resultMap = dbJudgeTool.deleteAllFuncTest(questionId);
		
		if (!"success".equals(resultMap.get("state"))) {
			return BaseResponse.fail(resultMap);
		}
		
		return BaseResponse.success(resultMap);
	}
	
	/* 修改单条测试用例 */
	@PostMapping(value = "/updateOneTestCase")
	public BaseResponse<Map<String, Object>> updateOneTestCase(@RequestParam(value = "teacherId") String teacherId,
			                                                   @RequestParam(value = "caseId") int caseId,
			                                                   @RequestParam(value = "questionId") int questionId,
			                                                   @RequestParam(value = "testCase") String test_case) {
		
		Map<String, Object> modelMap = new HashMap<>();
		Map<String, Object> resultMap;
		DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(setKeyForJudgeConnect(teacherId));
		if (dbConnection == null) {
			modelMap.put("errmessage", "连接超时, 请重连数据库");
			return BaseResponse.fail(modelMap);
		}
		
		Connection connection;
		try {
			 connection = dbConnection.getConnect();
		} catch (SQLException e) {
			modelMap.put("errmessage", "无法连接数据库");
			return BaseResponse.fail(modelMap);
		}
		
		DBJudgeTool dbJudgeTool = new DBJudgeTool(connection);
		resultMap = dbJudgeTool.updateFuncTest(caseId, questionId, test_case);
		
		if (!"success".equals(resultMap.get("state"))) {
			return BaseResponse.fail(resultMap);
		}
		
		return BaseResponse.success(resultMap);
	}
	
	/* 查询指定题目全部测试用例 */
	@PostMapping(value = "/selectTestCase")
	public BaseResponse<Map<String, Object>> selectTestCase(@RequestParam(value = "teacherId") String teacherId,
                                                               @RequestParam(value = "questionId") int questionId) {

         Map<String, Object> modelMap = new HashMap<>();
         List<Map<String, Object>> caseList;
         DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(setKeyForJudgeConnect(teacherId));
         if (dbConnection == null) {
			 modelMap.put("errmessage", "连接超时, 请重连数据库");
             return BaseResponse.fail(modelMap);
         }

         Connection connection;
         try {
             connection = dbConnection.getConnect();
         } catch (SQLException e) {
			 modelMap.put("errmessage", "无法连接数据库");
             return BaseResponse.fail(modelMap);
         }

         DBJudgeTool dbJudgeTool = new DBJudgeTool(connection);
         caseList = dbJudgeTool.selectFuncTest(questionId);
         modelMap.put("caseList", caseList);
         
         return BaseResponse.success(modelMap);
    } 
	
	/* 设置评判数据库缓存关键字 */
	private String setKeyForJudgeConnect(String teacherId) {
		return teacherId+"_Judge";
	}

}
